SET SERVEROUTPUT ON SIZE 1000000;
SET VERIFY OFF

DECLARE
	l_total_extracted	NUMBER;
	l_total_imported	NUMBER;	
	l_total_extracted_2 NUMBER;			
	l_total_imported_2 NUMBER;
	l_func_id			VARCHAR2(100):='CAPPOREC_ITEM';
	l_code VARCHAR2(100):=UPPER('&1');

BEGIN
	 select COUNT(1)
	 into l_total_extracted
       from   mtl_system_items_b msib,
              org_organization_definitions ood
       where  msib.organization_id = ood.organization_id 
       and    ood.set_of_books_id = (select set_of_books_id
     							     from   gl_sets_of_books
     								 where  upper(short_name) = upper(l_code));
	
	SELECT COUNT(1) 
	INTO l_total_imported
	FROM CRST_MTL_SYSTEM_ITEMS_B;

	select COUNT(1)
	INTO l_total_extracted_2
       from   mtl_system_items_b msib, 
              mtl_system_items_tl dtl,
              org_organization_definitions ood
       where  msib.organization_id = ood.organization_id 
       and    ood.set_of_books_id = (select set_of_books_id
     							     from   gl_sets_of_books
     								 where  upper(short_name) = upper(l_code))
       and    msib.organization_id = dtl.organization_id
       and    msib.inventory_item_id = dtl.inventory_item_id;

	SELECT COUNT(1) 
	INTO l_total_imported_2
	FROM CRST_MTL_SYSTEM_ITEMS_TL;

	dbms_output.put_line('Function: '||l_func_id);		
	dbms_output.put_line('Total Extracted: '||TO_CHAR(l_total_extracted)) ;
	dbms_output.put_line('Total Imported:  '||TO_CHAR(l_total_imported));
	dbms_output.put_line('Total Extracted_2: '||TO_CHAR(l_total_extracted_2)) ;
	dbms_output.put_line('Total Imported_2:  '||TO_CHAR(l_total_imported_2));

	IF l_total_extracted <> l_total_imported THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;	

	IF l_total_extracted_2 <> l_total_imported_2 THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;	
						
EXCEPTION
	WHEN OTHERS THEN	
		dbms_output.put_line('Function Error: '||l_func_id);			
END;
/
